from urllib.parse import quote

import xlwt
import io

from django.db import connection
from django.shortcuts import render
from django.http import HttpResponse, HttpRequest, JsonResponse
from django.utils import timezone

from polls.models import Record
from django.db.models import Q
import re


def index(request: HttpRequest) -> HttpResponse:
    return render(request, 'index.html')


def search(request: HttpRequest) -> HttpResponse:
    page = int(request.GET.get('page', '1'))
    size = int(request.GET.get('size', '5'))
    # 一对一、多对一外键关联，可以通过select_related（“关联对象”）解决1+n查询问题
    # 多对多外键关联，可以通过prefetch_related（“关联对象”）解决1+n查询问题
    # 通过queryset对象的only指定哪些需要投影的字段
    # 通过queryset对象的defer指定不需要投影的字段er
    queryset = Record.objects.filter(is_deleted=False)\
        .defer('is_deleted', 'deleted_time', 'updated_time')\
        .select_related('car')
    carinfo = request.POST.get('carinfo')
    if carinfo:
        carinfo = re.sub(r'\s', '', carinfo)
        queryset = queryset.filter(
            Q(car__carno__istartswith=carinfo) | Q(car__owner__icontains=carinfo)
        )
    count = queryset.count()
    queryset = queryset.order_by('-makedate')[(page - 1) * size:page * size]
    total_page = (count - 1) // size + 1
    return render(request, 'index.html', {
        'records': queryset,
        'current_page': page,
        'page_size': size,
        'total_page': total_page,
        'has_prev': page > 1,
        'has_next': page < total_page,
        'prev_page': page - 1,
        'next_page': page + 1,
        'carinfo': carinfo
    })


def handle_record(request: HttpRequest) -> HttpResponse:
    try:
        rno = int(request.GET.get('rno'))
        record = Record.objects.filter(no=rno, dealt=False).first()
        if record and not record.dealt:
            record.dealt = True
            record.updated_time = timezone.now()
            record.save()
            data = {'code': 10000, 'message': 'dealt successfully'}
        else:
            data = {'code': 10001, 'message': 'dealt false'}
    except ValueError:
        data = {'code': 10002, 'message': 'invalid car number'}
    return JsonResponse(data)


def delete_record(request: HttpRequest) -> HttpResponse:
    try:
        rno = int(request.GET.get('rno'))
    except ValueError:
        data = {'code': 20002, 'message': 'invalid car number'}
    else:
        record = Record.objects.filter(no=rno, dealt=True).first()
        if record:
            if record.dealt:
                record.is_deleted = True
                record.deleted_time = timezone.now()
                record.save()
                data = {'code': 20000, 'message': 'delete successfully'}
            else:
                data = {'code': 20001, 'message': 'can not delete which is not dealt'}
        else:
            data = {'code': 20003, 'message': 'delete false'}
    return JsonResponse(data)


def export_excel(request: HttpRequest) -> HttpResponse:
    queryset = Record.objects.filter(is_deleted=False)\
        .defer('is_deleted', 'deleted_time', 'updated_time')\
        .select_related('car').order_by('no')
    wb = xlwt.Workbook()
    sheet = wb.add_sheet('违章记录表')
    titles = ('编号', '车牌号', '车主姓名', '违章原因', '违章时间', '处罚方式', '是否受理')
    for col_index, title in enumerate(titles):
        sheet.write(0, col_index, title)
    for row_index, record in enumerate(queryset):
        sheet.write(row_index + 1, 0, record.no)
        sheet.write(row_index + 1, 1, record.car.no)
        sheet.write(row_index + 1, 2, record.car.owner)
        sheet.write(row_index + 1, 3, record.reason)
        sheet.write(row_index + 1, 4, record.makedate.strftime('%Y-%m-%d'))
        sheet.write(row_index + 1, 5, record.punish)
        sheet.write(row_index + 1, 6, '已受理' if record.dealt else '未受理')
    buffer = io.BytesIO()
    wb.save(buffer)
    resp = HttpResponse(buffer.getvalue(), content_type='application/vnd.ms-excel')
    filename = quote('违章记录汇总统计.xls')
    resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
    return resp


def get_bar_data(request: HttpRequest) -> HttpResponse:
    with connection.cursor() as cursor:
        cursor.execute('select carno, ifnull(total, 0) as total from tb_car t1 '
                       ' left outer join (select car_id, count(no) as total from '
                       ' tb_record group by car_id) t2 on t1.no = t2.car_id')

        xdata, ydata = [], []
        for row in cursor.fetchall():
            xdata.append(row[0])
            ydata.append(row[1])
    return JsonResponse({'xdata': xdata, 'ydata': ydata})